package com.cpi.cornpp.poplanned.reals.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.poplanned.reals.dto.PoPlannedRealsDTO;
import com.cpi.cornpp.poplanned.reals.exception.PoPlannedRealsException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Po Planned Reals Data Access
 * @author Edgar Joao
 * 
 */
public class PoPlannedRealsDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** Exist Planned Reals */
	private static final String SQL_EXIST_POPLANNED_REALS = "SELECT * FROM po_planned_reals where purchase_order_id = ?";//$NON-NLS-1$	
	/** SQL Insert Po Planned Reals */
	private static final String SQL_INSERT_POPLANNED_REALS 	= "INSERT INTO po_planned_reals (purchase_order_id, po_number) VALUES(?,?)";//$NON-NLS-1$
	/** SQL Update Po Planned Reals */
	private static final String SQL_UPDATE_POPLANNED_REALS 	= "UPDATE po_planned_reals SET po_number = ? WHERE purchase_order_id = ?";//$NON-NLS-1$
	/** SQL Delete Po Planned Reals */
	private static final String SQL_DELETE_POPLANNED_REALS = "DELETE FROM po_planned_reals WHERE purchase_order_id = ?";//$NON-NLS-1$
	/** SQL Get Po Planned Reals */
	private static final String SQL_GET_POPLANNED_REALS = "SELECT po_number FROM po_planned_reals WHERE purchase_order_id = ?";//$NON-NLS-1$
	
	/**
	 * Exist Plannned Reals
	 * @param entity
	 * @return
	 * @throws PoPlannedRealsException
	 */
	public boolean existPlannedReals(PoPlannedRealsDTO entity) throws PoPlannedRealsException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SQL_EXIST_POPLANNED_REALS, DATASOURCE);
			preparedStatement.setInt(1, entity.getPurchaseOrderId());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {				
				result=true;							
			}else{
				result=false;
			}
		}catch (PoolException poolException){
			PoPlannedRealsException poPlannedRealsException = null;
			poolException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw poPlannedRealsException;		
		}catch (SQLException sqlException){
			PoPlannedRealsException poPlannedRealsException = null;
			sqlException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw poPlannedRealsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	
	/**
	 * Insert PoPlanned Reals
	 * @param entity
	 * @return
	 * @throws PoPlannedRealsException
	 */
	public void addPlannedReals(PoPlannedRealsDTO entity) throws PoPlannedRealsException{
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_INSERT_POPLANNED_REALS, DATASOURCE);			
			preparedStatement.setInt(1, entity.getPurchaseOrderId());
			preparedStatement.setString(2, entity.getPoNumber());			
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				PoPlannedRealsException poPlannedRealsException = null;
				poPlannedRealsException = new PoPlannedRealsException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);				
				throw poPlannedRealsException;				
			}
			
		}catch (PoolException poolException){
			PoPlannedRealsException poPlannedRealsException = null;
			poolException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw poPlannedRealsException;			
		}catch (SQLException sqlException){
			PoPlannedRealsException poPlannedRealsException = null;
			sqlException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw poPlannedRealsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}		
	}
	
	
	/**
	 * Update PoPlanned Reals
	 * @param entity
	 * @return
	 * @throws PoPlannedRealsException
	 */
	public void updatePlannedReals(PoPlannedRealsDTO entity) throws PoPlannedRealsException{
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_UPDATE_POPLANNED_REALS, DATASOURCE);
						
			preparedStatement.setString(1, entity.getPoNumber());
			preparedStatement.setInt(2, entity.getPurchaseOrderId());	
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				PoPlannedRealsException poPlannedRealsException = null;
				poPlannedRealsException = new PoPlannedRealsException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);				
				throw poPlannedRealsException;				
			}
			
		}catch (PoolException poolException){
			PoPlannedRealsException poPlannedRealsException = null;
			poolException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw poPlannedRealsException;			
		}catch (SQLException sqlException){
			PoPlannedRealsException poPlannedRealsException = null;
			sqlException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw poPlannedRealsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}		
	}
	
	
	/**
	 * 	
	 * Delete Planned Reals
	 * @param entity
	 * @throws PoPlannedRealsException
	 */
	public void deletePlannedReals(PoPlannedRealsDTO entity) throws PoPlannedRealsException{
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try {
			preparedStatement =  prepareSQL(SQL_DELETE_POPLANNED_REALS, DATASOURCE);
						
			preparedStatement.setInt(1, entity.getPurchaseOrderId());
			//preparedStatement.setString(2, entity.getPoNumber());				
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				PoPlannedRealsException poPlannedRealsException = null;
				poPlannedRealsException = new PoPlannedRealsException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);				
				throw poPlannedRealsException;				
			}
			
		}catch (PoolException poolException){
			PoPlannedRealsException poPlannedRealsException = null;
			poolException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw poPlannedRealsException;			
		}catch (SQLException sqlException){
			PoPlannedRealsException poPlannedRealsException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if(sqlException.getErrorCode() == systemParameterBean.getIntValue()){
				poPlannedRealsException = new PoPlannedRealsException (DBDAO.FOREIGN_KEY_ERROR_KEY , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}else{
				poPlannedRealsException = new PoPlannedRealsException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}
			sqlException.printStackTrace(System.out);
			throw poPlannedRealsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	
	/**
	 * Get a PoPlanned Reals
	 * @param entity
	 * @return
	 * @throws PoPlannedRealsException
	 */
	public PoPlannedRealsDTO getPoPlannedRealsById(PoPlannedRealsDTO entity) throws PoPlannedRealsException{
		PreparedStatement preparedStatement =  null;
		PoPlannedRealsDTO poPlannedRealsDTO = null;
		try {
			preparedStatement =  prepareSQL(SQL_GET_POPLANNED_REALS, DATASOURCE);						
			preparedStatement.setInt(1, entity.getPurchaseOrderId());			
			
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {	
				poPlannedRealsDTO = new PoPlannedRealsDTO();
				poPlannedRealsDTO.setPoNumber(rst.getString(1));				
			}else{
				poPlannedRealsDTO = new PoPlannedRealsDTO();
			}
			
		}catch (PoolException poolException){
			PoPlannedRealsException poPlannedRealsException = null;
			poolException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw poPlannedRealsException;			
		}catch (SQLException sqlException){
			PoPlannedRealsException poPlannedRealsException = null;
			sqlException.printStackTrace(System.out);
			poPlannedRealsException = new PoPlannedRealsException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw poPlannedRealsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return poPlannedRealsDTO;
	}
}
